Catalog Functions

Because catalog functions, such as those listed here, are slow compared to other ODBC functions, their frequent use can impair system performance:

SQLColumns SQLColumnPrivileges SQLForeignKeys SQLProcedures SQLProcedureColumns

SQLSpecialColumns SQLStatistics SQLTables SQLTablePrivileges

Minimizing the Use of Catalog Functions

Compared to other ODBC functions, catalog functions are relatively slow. By caching information, applications can avoid multiple executions. Although it is almost impossible to write an ODBC application without catalog functions, their use should be minimized.

To return all result column information mandated by the ODBC specification, a driver may have to perform multiple queries, joins, subqueries, and unions to return the required result set for a single call to a catalog function. These particular elements of the SQL language are performance expensive.

Applications should cache information from catalog functions so that multiple executions are unnecessary. For example, call SQLGetTypeInfo once in the application and cache the elements of the result set that your application depends on. It is unlikely that any application uses all elements of the result set generated by a catalog function, so the cached information should not be difficult to maintain.

Avoiding Search Patterns

Passing null arguments or search patterns to catalog functions generates time-consuming queries. In addition, network traffic potentially increases because of unwanted results. Always supply as many non-null arguments to catalog functions as possible. Because catalog functions are slow, applications should invoke them efficiently. Any information that the application can send the driver when calling catalog functions can result in improved performance and reliability.

For example, consider a call to SQLTables where the application requests information about the table "Customers." Often, this call is coded as shown, using the fewest non-null arguments necessary for the function to return success:

rc = SQLTables (NULL, NULL, NULL, NULL, "Customers", SQL_NTS, NULL); 

A driver may process this SQLTables call into SQL as shown:

SELECT ... FROM SysTables WHERE TableName = 'Customers' UNION ALL 
SELECT ... FROM SysViews WHERE ViewName = 'Customers' UNION ALL 
SELECT ... FROM SysSynonyms WHERE SynName = 'Customers' 
   ORDER BY ... 

In this example, the application provided little information about the object for which information was requested. Suppose three "Customers" tables were returned in the result set:

The first table was owned by the user.
The second table was owned by the sales department.
The third table was a view created by management.

It may not be obvious to the user which table to choose. If the application had specified the OwnerName argument for the SQLTables call, only one table would be returned and performance would improve. Less network traffic would be required to return only one result row and unwanted rows would be filtered by the database.

In addition, if the TableType argument can be supplied, the SQL sent to the server can be optimized from a three-query union to a single Select statement as shown:

SELECT ... FROM SysTables  
WHERE TableName = 'Customers' and Owner = 'Beth' 

Determining Table Characteristics with a Dummy Query

Avoid using SQLColumns to determine table characteristics. Instead, use a dummy query with SQLDescribeCol.

Consider an application that allows the user to choose the columns that will be selected. Should the application use SQLColumns to return information about the columns to the user or prepare a dummy query and call SQLDescribeCol?

Case 1: SQLColumns Method

rc = SQLColumns (... "UnknownTable" ...); 
// This call to SQLColumns will generate a query to 
// the system catalogs... possibly a join which must be 
// prepared, executed, and produce a result set 
rc = SQLBindCol (...); 
rc = SQLExtendedFetch (...); 
// user must retrieve N rows from the server 
// N = # result columns of UnknownTable 
// result column information has now been obtained 

Case 2: SQLDescribeCol Method

// prepare dummy query  
rc = SQLPrepare (... "SELECT * from UnknownTable 
    WHERE 1 = 0" ...); 
// query is never executed on the server - only prepared 
rc = SQLNumResultCols (...); 
for (irow = 1; irow <= NumColumns; irow++) { 
   rc = SQLDescribeCol (...) 
   // + optional calls to SQLColAttributes 
   } 
// result column information has now been obtained 
// Note we also know the column ordering within the table! 
// This information cannot be 
// assumed from the SQLColumns example. 

In both cases, a query is sent to the server. In Case 1, the query must be evaluated and form a result set that is returned to the client. Case 2 is the better performing model.

To complicate this discussion, let us consider a database server that does not natively support preparing a SQL statement. The performance of Case 1 does not change, but the performance of Case 2 improves slightly, because the dummy query is evaluated before being prepared. Because the Where clause of the query always evaluates to FALSE, the query generates no result rows and is processed without accessing table data. Again, Case 2 outperforms Case 1.

Managing the Retrieval of Database Meta-Information

Meta-information is information that describes the data stored in the database and can include information about the tables in the database, the columns in those tables, and the indexes that are defined for those tables. This data also is referred to as the database's data dictionary or system catalog.

Typically, ODBC applications extract and use information from the database's data dictionary using specific calls, such as the ODBC calls SQLTables, SQLColumns, and SQLPrimaryKeys. In large databases, the amount of meta-information that is retrieved can be considerable. Because some client applications cannot manage large amounts of information efficiently, system performance can be adversely affected.

Some ODBC calls have parameters that accept search patterns. You can use these parameters to limit the amount of meta-information that is retrieved; however, not every client application supports these parameters.

SequeLink allows you to use database data dictionary filters and database data dictionary views to limit the amount of meta-information that is retrieved.

Using Database Data Dictionary Filters

Database Data Dictionary filters limit the amount of meta-information that can be retrieved from the database's native data dictionary. Specifically, they limit the number of result rows that can be returned for SQLTables. The data dictionary filters override any call parameters that are passed by the application when it accesses the database's native data dictionary.

SequeLink provides the following types of database data dictionary filters, which must be defined on the server:

Filter by catalog list
Filter by schema list
Filter by table type

For more information about setting the database data dictionary filters for a SequeLink service, refer to the SequeLink Administrator's Guide.